package com.topisv.tms.service.work;

import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * 
 * @author
 *
 */
@Service
@Transactional(readOnly = true)
public class SendCountService {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	/**
	 * 
	 * @param startTime
	 * @param endTime
	 * @param customerCode
	 * @return
	 */
	public List<Map<String, Object>> getSendCount(String startTime, String endTime,
			String customerCode, String wcode) {
		StringBuilder sb = new StringBuilder();
		
		sb.append("SELECT express_company,count(1) as num FROM `work_order` where cancel_status='ok' and");
		if (StringUtils.isNotEmpty(customerCode)) {
			sb.append(" customer_code in ('");
			String customerCodes = customerCode.replaceAll(":", "','");
			sb.append(customerCodes);
			sb.append("') AND ");
		}
		sb.append(" warehouse_code=? and finish_time BETWEEN ? AND ? and status='6'  GROUP BY express_company");
		
		Object[] params = null;
		params = new Object[] { wcode, startTime, endTime};
		return jdbcTemplate.queryForList(sb.toString(), params);
	}

	/**
	 * 
	 * @param startTime
	 * @param endTime
	 * @param customerCode
	 * @return
	 */
	public List<Map<String, Object>> getSendCountByCustomerCode(String startTime, String endTime,
			String customerCode, String wcode) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT express_company,count(1) as num FROM `paiuwms`.`work_order` where cancel_status='ok' and");
		if (StringUtils.isNotEmpty(wcode)) {
			sb.append(" warehouse_code= ? AND ");
		}
		sb.append(" customer_code=? and create_time BETWEEN ? AND ?  GROUP BY express_company");
		Object[] params = null;
		if (StringUtils.isNotEmpty(wcode)) {
			params = new Object[] { wcode, customerCode, startTime, endTime };
		} else {
			params = new Object[] { customerCode, startTime, endTime };
		}
		return jdbcTemplate.queryForList(sb.toString(), params);
	}

	/**
	 * 作业订单统计
	 * 
	 * @param startTime
	 * @param endTime
	 * @param customerCode
	 * @param wcode
	 * @return
	 */
	public List<Map<String, Object>> getOrderStatusCountByCustomerCode(String startTime,
			String endTime, String customerCode, String wcode) {
		StringBuilder sb = new StringBuilder();
		// 1.创建批次 2.创建任务 3.已拣货 4.已灯光拣选 9.待包装 5.已包装 6.已发货 7.已签收 8.异常
		sb.append("select case status1 ");
		sb.append(" when 1 then '创建批次'");
		sb.append(" when 2 then '创建任务'");
		sb.append(" when 3 then '已拣货'");
		sb.append(" when 4 then '已灯光拣选'");
		sb.append(" when 5 then '待包装'");
		sb.append(" when 6 then '已包装'");
		sb.append(" when 7 then '已发货'");
		sb.append(" end as status,num ");
		sb.append(" from ( ");
		sb.append("select case status ");
		sb.append(" when 1 then 1");
		sb.append(" when 2 then 2");
		sb.append(" when 3 then 3");
		sb.append(" when 4 then 4");
		sb.append(" when 9 then 5");
		sb.append(" when 5 then 6");
		sb.append(" when 6 then 7");
		sb.append(" end as status1,count(*) as num");
		sb.append(" FROM work_order where cancel_status='ok' and ");
		if (StringUtils.isNotEmpty(wcode)) {
			sb.append(" warehouse_code= '" + wcode + "' AND ");
		}
		if (StringUtils.isNotEmpty(customerCode)) {
			sb.append(" customer_code= '" + customerCode + "' AND ");
		}
		sb.append("  ((create_time BETWEEN '" + startTime + "' AND '" + endTime +"' and status!= '6')");
		sb.append(" OR finish_time BETWEEN '" + startTime + "' AND '" + endTime
				+ "') and status in ('1','2','3','4','9','5','6')  GROUP BY status");
		sb.append(" ) a order by status1 ");
		return jdbcTemplate.queryForList(sb.toString());
	}

	public List<Map<String, Object>> getRepeatOrderByDate(String startTime, String endTime) {
		StringBuilder sb = new StringBuilder();
		sb.append("select customer_ordeno,num from ");
		sb.append("(SELECT customer_ordeno,count(*) num ");
		sb.append(" FROM  edi_order ");
		sb.append("where customer_code ='KLY' ");
		sb.append("and order_status='ok'  ");
		sb.append(" and  crtime BETWEEN '" + startTime + "' AND '" + endTime + "' ");
		sb.append(" group by customer_ordeno ) a where a.num>1");
		return jdbcTemplate.queryForList(sb.toString());
	}
}
